#!/usr/bin/env python
import sys

sys.path.append("/work/opt/analytic/my_python")
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from lib.load_data import *
import re
import time

reload(sys)
sys.setdefaultencoding('utf-8')


def get_top_info_data(date):
    print 'get top info data begin'
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_info"
    line_list = []
    query = "select topinfoid,topid,userid,toptype,cateid,localid,tophour,starttime,endtime,addtime,state,amount,topsortid,payamount from topinfo_topinfo where date(addtime)='%s' order by topinfoid asc" % date
    #print query
    items = conn.get_results(query)
    for item in items:
        top_info_id = item[0]
        top_id = item[1]
        user_id = item[2]
        top_type = item[3]
        cate_id = item[4]
        city = item[5]
        top_hour = item[6]
        start_time = item[7]
        end_time = item[8]
        add_time = item[9]
        state = item[10]
        amount = item[11]
        top_sortid = item[12]
        pay_amount = item[13]

        query_in = "select infoid from topinfo_infolog where topid=%s" % top_id
        items_in = conn.get_results(query_in)
        info_id = 0
        if len(items_in) > 0:
            info_id = items_in[0][0]

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]

        dc = get_cates(cate_id, stat_conn)
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name = ''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name = dc[1][1]

        query = 'select count(1) from portal_info where user_id=%s' % user_id
        its = stat_conn.get_results(query)
        user_type = its[0][0]
        if user_type > 0:
            user_type = 1

        if amount == 0:
            query = 'select all_consume from top_info_project where top_id=%s' % top_id
            its = stat_conn.get_results(query)
            if len(its) > 0:
                amount = its[0][0]

        cash_amount = 0
        discount_amount = 0

        q = "select ifnull(cash_amount-refund_cash_amount,0),ifnull(discount_amount-refund_discount_amount,0) from top_info_pay where top_info_id=%s" % top_info_id
        its = stat_conn.get_results(q)
        if len(its) > 0:
            cash_amount = its[0][0]
            discount_amount = its[0][1]

        param = ["%s" % top_info_id, "%s" % top_id, "%s" % user_id, "%s" % user_type, "%s" % city_id, city_name,
                 "%s" % area_id, area_name, "%s" % local_id, local_name, "%s" % cat1, cat1name, "%s" % cat2, cat2name,
                 '0', '', "%s" % add_time, "%s" % top_type, "%s" % amount, "%s" % pay_amount, "%s" % top_hour,
                 "%s" % start_time, "%s" % end_time, "%s" % state, "%s" % top_sortid, "%s" % info_id,
                 "%s" % cash_amount, "%s" % discount_amount]
        line_list.append('\t'.join(param))
        #stat_conn.insert_record(stat_table,param)

    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/topinfo_data/"
    file_name = "top_info_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    q = "delete from top_info where date(pay_date)='%s'" % date
    stat_conn.execute_sql(q)

    load_data(stat_conn, path + file_name, stat_table, '\t')
    conn.close()
    stat_conn.close()
    print "%s : done" % date


def get_top_info_project_data(date):
    print 'get_top_info_project_dataa begin'
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_info_project"
    line_list = []
    query = "select projectid,topid,project,allconsume,state from topinfo_project where date(createtime)='%s'" % date
    #print query
    items = conn.get_results(query)
    for item in items:
        project_id = item[0]
        top_id = item[1]
        project = item[2]
        all_consume = item[3]
        state = item[4]

        param = [project_id, top_id, project, all_consume, state]
        line_list.append('\t'.join(map(lambda i: str(i), param)))
        #stat_conn.insert_record(stat_table,param)

    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/topinfo_project/"
    file_name = "top_info_project_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    load_data(stat_conn, path + file_name, stat_table, '\t')
    conn.close()
    stat_conn.close()
    print "%s : done" % date


def get_top_info_price_data(date):
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    ch_conn = set_channel_conn()
    stat_table = "top_info_price"

    line_list = []

    #
    sep = '-'
    fmt = sep.join(['%Y', '%m', '%d'])
    start_date = time.strptime('2010-01-01', fmt)
    today = time.strptime(get_today(), fmt)
    today_seconds = (int)(time.mktime(today) - time.mktime(start_date))
    print today_seconds

    query = "select configid,localid,cateid,pricenum,effid,failid,addtime from topinfo_specialprice where failid>%s order by configid asc " % today_seconds
    #print query
    items = conn.get_results(query)
    for item in items:
        config_id = item[0]
        cate_id = item[2]
        city = item[1]
        price_num = item[3]
        eff_id = item[4]
        fail_id = item[5]
        add_time = item[6]

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        channel_key = ""
        channel_id = 0
        channel_name = ""
        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
                channel_key = "%s" % city_id
                channel_name = city_name
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
                channel_key = "%s" % area_id
                channel_name = area_name
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]
                channel_key = "%s" % local_id
                channel_name = local_name

        dc = get_cates(cate_id, stat_conn)
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name = ''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name = dc[1][1]

        eff_time = format_timestamp(eff_id)
        fail_time = format_timestamp(fail_id)

        #get channel info
        if channel_key != "":
            channel_key += "&%s" % cat2
            channel_name += "-%s" % cat2name

            query_in = "select channel_id,channel_name from channel_profile where channel_key='%s'" % channel_key
            #print query_in
            items_in = ch_conn.get_results(query_in)
            if len(items_in):
                channel_id = items_in[0][0]
                channel_name = items_in[0][1]

        param = [config_id, city_id, city_name, area_id, area_name, local_id, local_name, cat1, cat1name, cat2,
                 cat2name, price_num, add_time, eff_time, fail_time, eff_id, fail_id, channel_id, channel_name]
        #insert_sql = " insert into %s(config_id,city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,price_num,add_time,eff_time,fail_time,eff_id,fail_id )" %stat_table
        #insert_sql += " values(%s,%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',%s,'%s',DATE_ADD('2010-01-01', INTERVAL %s/86400 DAY),DATE_ADD('2010-01-01', INTERVAL %s/86400 DAY),%s,%s) "%param
        line_list.append('\t'.join(map(lambda i: str(i), param)))

    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/topinfo_data/price/"
    file_name = "top_info_price_stats_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)
    query = "truncate table top_info_price"
    stat_conn.execute_sql(query)

    load_data(stat_conn, path + file_name, stat_table, '\t')

    conn.close()
    ch_conn.close()
    stat_conn.close()


def format_timestamp(ts):
    ts += 1262304000
    return time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(ts))


def get_top_info_cate_stat(date):
    conn = set_stats_conn();
    stat_table = "top_info_cate_stats"
    line_list = []
    #today = '2012-09-02'
    today = var_date(date, "-", "+", 1)
    #return 
    query = "select city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,user_type,count(distinct user_id),count(distinct top_id),count(1),sum(amount/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))),sum(top_hour/if(datediff(end_time,start_time)<1,1,(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time))/(UNIX_TIMESTAMP(if('%s'>end_time,end_time,'%s'))-UNIX_TIMESTAMP(if('%s'<start_time,start_time,'%s'))))) from top_info where date(start_time)<='%s' and date(end_time)>='%s' group by city_id,area_id,local_id,cate1_id,cate2_id,cate3_id,user_type " % (
        today, today, date, date, today, today, date, date, date, date)
    items = conn.get_results(query)
    #print query
    #return
    for item in items:
        city_id = item[0]
        city_name = item[1]
        area_id = item[2]
        area_name = item[3]
        local_id = item[4]
        local_name = item[5]

        cat1 = item[6]
        cat1name = item[7]
        cat2 = item[8]
        cat2name = item[9]
        cat3 = item[10]
        cat3name = item[11]

        user_type = item[12]
        user_count = item[13]
        info_count = item[14]
        record_count = item[15]
        pay_amount = item[16]
        total_top_hour = item[17]

        avg_amount = 0
        if pay_amount == None:
            pay_amount = 0
            #elif user_count > 0:
            #avg_amount = pay_amount/user_count
        avg_amount = pay_amount

        param = [date, "%s" % city_id, city_name, "%s" % area_id, area_name, "%s" % local_id, local_name, "%s" % cat1,
                 cat1name, "%s" % cat2, cat2name, '0', '', "%s" % user_count, "%s" % info_count, "%s" % record_count,
                 "%s" % avg_amount, "%s" % total_top_hour, "%s" % user_type]
        #print param;
        line_list.append('\t'.join(param))

    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/topinfo_data/tanxiao/"
    file_name = "top_info_cate_stats_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    load_data(conn, path + file_name, stat_table, '\t')

    conn.close()


def copy_from_mongo(date):
    mongo_conn = set_mongo_conn()
    print "OK"
    stat_conn = set_stats_conn()

    line_list = []
    log_table = "toptanxiao"
    stat_table = 'top_info_tanxiao_stats'

    query = {'date': date}
    items = find_items(mongo_conn, log_table, query)
    #print len(items)
    #return
    for item in items:
        logid = item['_id']
        #print logid
        cate1id = item['cate1id']
        cate1name = item['cate1name']
        cateid = item['cateid']
        catename = item['catename']
        city = item['cityid']
        #cityname = item[6]
        #date = item[7]
        infonum = item['infonum']
        oh = item['oh']
        sh = item['sh']
        tanxiao = item['tanxiao']
        usernum = item['usernum']

        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id = 0
        local_name = ''

        dc = get_citys(city, stat_conn)
        #print city
        if dc != None:
            if len(dc) > 0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc) > 1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc) > 2:
                local_id = dc[2][0]
                local_name = dc[2][1]

        cate1name = cate1name.encode('utf-8')
        catename = catename.encode('utf-8')

        param = [date, logid, city_id, city_name, area_id, area_name, local_id, local_name, cate1id, cate1name, cateid,
                 catename, infonum, oh, sh, tanxiao, usernum]
        #print param
        #stat_conn.insert_record(stat_table,param)
        line_list.append('\t'.join(map(lambda i: str(i), param)))

    cont = '\n'.join(line_list)
    path = "/work/opt/analytic/lh_tmp_data/topinfo_data/tanxiao/"
    file_name = "top_info_mongo_stats_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)
    print 'loading data'
    load_data(stat_conn, path + file_name, stat_table, '\t')
    stat_conn.close()
    #pass


def get_citys(city, conn):
    if city == 0:
        return None

    query = 'select fullpath from t_local where localid=%s' % city
    items = conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select localid, localname from t_local where localid in (%s) order by depth asc" % fullpath
    #print q
    return conn.get_results(query)


def get_cates(cate, conn):
    if cate == 0:
        return None

    query = 'select fullpath from t_category where cateid=%s' % cate
    items = conn.get_results(query)
    fullpath = None
    if len(items) > 0:
        fullpath = items[0][0]

    if fullpath is None:
        return None

    query = "select cateid, catename from t_category where cateid in (%s) order by depth asc" % fullpath
    #print q
    return conn.get_results(query)


def get_top_info_tmp_data(date):
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_info_specialprice"

    query = "select city_id,ifnull(area_id,0),ifnull(local_id,0),cate_id from tmp group by city_id,area_id,local_id,cate_id"
    #print query
    items = stat_conn.get_results(query)
    for item in items:
        city_id = item[0]
        area_id = item[1]
        local_id = item[2]
        cate_id = item[3]

        local = city_id
        if area_id > 0:
            local = area_id
        if local_id > 0:
            local = local_id

        query_in = "select pricenum from topinfo_specialprice where failid>100140800 and localid=%s and cateid=%s" % (
            local, cate_id)
        items_in = conn.get_results(query_in)
        if len(items_in) > 0:
            price = items_in[0][0]

            update_sql = "update tmp set price_num=%s where city_id=%s and area_id=%s and local_id=%s and cate_id=%s" % (
                price, city_id, area_id, local_id, cate_id)
            #print update_sql
            stat_conn.execute_sql(update_sql)
    conn.close()
    stat_conn.close()


def get_top_infoid(date):
    conn = set_top_conn()
    stat_conn = set_stats_conn();
    stat_table = "top_tmp"
    line_list = []
    query = "select topid,infoid from topinfo_infolog  order by topid asc"
    #print query
    items = conn.get_results(query)
    for item in items:
        top_id = item[0]
        info_id = item[1]

        param = ['%s' % top_id, '%s' % info_id]
        line_list.append('\t'.join(param))

    cont = '\n'.join(line_list)
    path = "./topinfo_data/"
    file_name = "top_info_tmp_%s.txt" % date.replace('-', "")
    writeTextToFile(path, file_name, "w", cont)

    load_data(stat_conn, path + file_name, stat_table, '\t')
    conn.close()
    stat_conn.close()


def execute(date):
    #get_top_info_price_data(date)
    try:
        pass
        copy_from_mongo(date)
    except Exception, e:
        print 'copy_from_mongo:', e

    try:
        pass
        get_top_info_project_data(date)
    except Exception, e:
        print 'get_top_info_project_data:', e

    try:
        pass
        get_top_info_data(date)
    except Exception, e:
        print 'get_top_info_data:', e
    try:
        pass
        get_top_info_cate_stat(date)
    except Exception, e:
        print 'get_top_info_cate_stat:', e
    try:
        pass
        #get_top_info_price_data(date)
    except Exception, e:
        print 'get_top_info_cate_stat:', e


def main():
    #date = '2012-05-28'
    #execute(date)
    if len(sys.argv) == 1:
        start_date = get_yesterday()
        end_date = start_date
    elif len(sys.argv) == 2:
        start_date = format_date(sys.argv[1])
        end_date = start_date
    elif len(sys.argv) == 3:
        start_date = format_date(sys.argv[1])
        end_date = format_date(sys.argv[2])
    stat_date = start_date
    while stat_date >= start_date and stat_date <= end_date:
        print "The program of the date of %s is beginning at %s " % (stat_date, get_now())
        #get_top_infoid(stat_dates)
        #get_top_info_project_data(stat_date)
        execute(stat_date)
        #get_top_info_data(stat_date)
        #get_top_info_cate_stat(stat_date)
        print "The program of the date of %s is end at %s " % (stat_date, get_now())
        stat_date = var_date(stat_date)


if __name__ == '__main__':
    main()
